package net.tycmc.cty.ctyms.timejob.iems.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.common.dao.ITransactionDAO;
import net.tycmc.bulb.common.util.MapGetter;
import net.tycmc.cty.ctyms.timejob.common.DateControl;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSDAO;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSTransDAO;

public class VclRoadInfoDAO {

	
	private IBaseDAO baseDAO;
	
	private ITransactionDAO tranDAO;
	
	public ITransactionDAO getTranDAO() {
		return tranDAO;
	}

	public void setTranDAO(ITransactionDAO tranDAO) {
		this.tranDAO = tranDAO;
	}

	/**
	 * @return the baseDAO
	 */
	public IBaseDAO getBaseDAO() {
		return baseDAO;
	}

	/**
	 * @param baseDAO the baseDAO to set
	 */
	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}
	/**
	 * 校验指定日期的“道路信息表”是否存在，如果不存在，则进行创建
	 * 重新统计时，则往正式的数据库道路信息日表插入数据
	 **/
	public boolean checkCreateRoadInfo(String thisWorkDay,String isbakTable) {
		boolean flag = false;
		String sql = "Select count(id) as counts FROM SysObjects Where name = 'Vcl_RoadInfo_"+thisWorkDay+"' and xtype = 'U'";
		if(Integer.parseInt(MapGetter.getString(this.getBaseDAO().execQueryOne(sql, new Object[]{}),"counts"))>0){
			flag = true;
		}else{
			sql = "create table dbo.Vcl_RoadInfo_"+thisWorkDay+"(VclRI_ID [decimal](18, 0) IDENTITY(1,1) NOT NULL,"
		        +"VclRI_Vcl_ID [decimal](18) not  NULL,"
				+"VclRI_Altitude [varchar](max) NULL,"
		        +"VclRI_GPSMile [varchar](max) NULL,"
				+"VclRI_WorkTime [varchar](max) NULL,"
		        +"VclRI_PstnTime [varchar](max) NULL,"
				+"VclRI_EgnWorkTime [varchar](max) NULL,"
				+"VclRI_WheelSpeed [varchar](max) NULL,"
				+"VclRI_MsgTime [varchar](max) NULL) ON [PRIMARY]";
			if(this.baseDAO.execUpdate(sql, new Object[]{})>-1){
				flag = true;
			}
		}
		//是否需要创建备份表--优化二期
		if(isbakTable.equals("1")){
			String bakSql = "create table dbo.Vcl_RoadInfo_"+thisWorkDay+"(VclRI_ID [decimal](18, 0) IDENTITY(1,1) NOT NULL,"
			        +"VclRI_Vcl_ID [decimal](18) not  NULL,"
					+"VclRI_Altitude [varchar](max) NULL,"
			        +"VclRI_GPSMile [varchar](max) NULL,"
					+"VclRI_WorkTime [varchar](max) NULL,"
			        +"VclRI_PstnTime [varchar](max) NULL,"
					+"VclRI_EgnWorkTime [varchar](max) NULL,"
					+"VclRI_WheelSpeed [varchar](max) NULL,"
					+"VclRI_MsgTime [varchar](max) NULL) ON [PRIMARY]";
			MyConnectDSDAO mucdd=new MyConnectDSDAO(true);
			int bakTable = mucdd.execUpdate(bakSql, new Object[]{});
			if(bakTable<0){
				System.out.println("道路信息创建备份表失败");
			}
		}
		return flag;	
	}
    /**
     * @param thisWorkDay：统计时间
     * 获取昨天道路信息中的，海拔、gps里程、工作时间、位置时间
     * */
	public List<Map<String, Object>> getVclPstnRoadInfo(String thisWorkDay,String VehcileIDS) {
		List<Map<String, Object>> list = null;
		
		StringBuilder sql = new StringBuilder("select VclP_Vcl_ID,VclP_Altitude,VclP_TMileage,MsgT_tWork,VclP_PstnTime from CTY_Vcl.dbo.Vcl_Pstn_"+thisWorkDay);
		sql.append(" left join CTY_Egn_B.dbo.Msg_Timing_"+thisWorkDay);
		sql.append(" on VclP_Vcl_ID= MsgT_Vcl_ID and MsgT_ID=VclP_MsgID");
		sql.append("  where VclP_MsgTp_ID=2 and MsgT_EgnState=1 and VclP_Speed>0  and VclP_Vcl_ID in ("+VehcileIDS+") order by VclP_PstnTime asc");
		System.out.println(sql.toString());
		list = baseDAO.execQuery(sql.toString(), new Object[]{});
		
		return list;
	}
    /**
     * @param thisWorkDay：统计时间
     * 获取昨天道路信息中的，海拔、gps里程、工作时间、位置时间
     * */
  public synchronized List<Map<String, Object>> getVclPstnRoadInfo_runner(String thisWorkDay,String vclIDS) {
		List<Map<String, Object>> list = null;

		StringBuilder sql = new StringBuilder("select VclP_Vcl_ID,VclP_Altitude,VclP_TMileage,MsgT_tWorkTime_UDS as MsgT_tWork,VclP_PstnTime from CTY_Vcl.dbo.Vcl_Pstn_"+thisWorkDay);
		sql.append(" left join CTY_Egn_B.dbo.Msg_Timing_"+thisWorkDay);
		sql.append(" on VclP_Vcl_ID= MsgT_Vcl_ID and MsgT_ID=VclP_MsgID");
		sql.append("  where VclP_MsgTp_ID=2 and MsgT_EgnState=1 and VclP_Speed>0  and VclP_Vcl_ID in ("+vclIDS+") order by VclP_PstnTime asc");
		//System.out.println(sql.toString());
		list = baseDAO.execQuery(sql.toString(), new Object[]{});
		
		return list;
	}
    /**
     * @param thisWorkDay：统计时间,vclIDS:设备id
     * 获取昨天道路信息中的pgn信息:总发动机操作时间、基于车轮的车辆速度
     * */
	public List<Map<String, Object>> getVclPgnRoadInfo(String thisWorkDay,String VehcileIDS) {
		List<Map<String, Object>> list = null;
		
		StringBuilder sql = new StringBuilder("SELECT msgp_vcl_id,TEMP.egntotalhours,TEMP.speed,msgp_msgtime FROM cty_egn_b.dbo.msg_pgn_"+thisWorkDay);
		sql.append(" INNER JOIN (SELECT Max(CASE msgpc_syspip_id WHEN 15 THEN msgpc_pgnvalue END) AS egntotalhours,");
		sql.append(" Max(CASE msgpc_syspip_id  WHEN 27 THEN msgpc_pgnvalue END) AS speed,msgpc_msgp_id FROM cty_egn_b.dbo.msg_pgn_content_"+thisWorkDay);
		sql.append(" where msgpc_syspip_id in (15,27) GROUP BY msgpc_msgp_id) TEMP ON msgp_id = msgpc_msgp_id WHERE msgp_vcl_id in ("+VehcileIDS+") and TEMP.speed IS NOT NULL AND TEMP.egntotalhours IS NOT NULL order by MsgP_MsgTime ");
		//System.out.println(sql.toString());
		list = baseDAO.execQuery(sql.toString(), new Object[]{});
		
		return list;
	}
    /**
     * @param thisWorkDay：统计时间,vclIDS:设备id
     * 获取昨天道路信息中的pgn信息:总发动机操作时间、基于车轮的车辆速度
     * */
	public synchronized List<Map<String, Object>> getVclPgnRoadInfo_runner(String thisWorkDay,String vclIDS) {
		List<Map<String, Object>> list = null;
		
		StringBuilder sql = new StringBuilder("SELECT msgp_vcl_id,TEMP.egntotalhours,TEMP.speed,msgp_msgtime FROM cty_egn_b.dbo.msg_pgn_"+thisWorkDay);
		sql.append(" INNER JOIN (SELECT Max(CASE msgpc_syspip_id WHEN 15 THEN msgpc_pgnvalue END) AS egntotalhours,");
		sql.append(" Max(CASE msgpc_syspip_id  WHEN 27 THEN msgpc_pgnvalue END) AS speed,msgpc_msgp_id FROM cty_egn_b.dbo.msg_pgn_content_"+thisWorkDay);
		sql.append(" where msgpc_syspip_id in (15,27) GROUP BY msgpc_msgp_id) TEMP ON msgp_id = msgpc_msgp_id WHERE msgp_vcl_id in ("+vclIDS+") and TEMP.speed IS NOT NULL AND TEMP.egntotalhours IS NOT NULL order by MsgP_MsgTime ");
		//System.out.println(sql.toString());
		list = baseDAO.execQuery(sql.toString(), new Object[]{});
		
		return list;
	}

	public void insertVclRoadInfo(String thisWorkDay,List<Map<String, Object>> resultList,List<String> sqlList) {
		
		StringBuilder sql = new StringBuilder();
		StringBuilder sql1 = new StringBuilder();
		StringBuilder sql2 = new StringBuilder();
		StringBuilder sql3 = new StringBuilder();
		StringBuilder sql4 = new StringBuilder();
		StringBuilder sql5 = new StringBuilder();
		Map<String,Object> mapRes = new HashMap<String, Object>();
		
		if(null != resultList && resultList.size() > 0){
			for(int i=0;i<resultList.size();i++){
				mapRes = resultList.get(i);
				sql.append("insert into Vcl_RoadInfo_"+thisWorkDay);
				sql1.append(" values('"+MapGetter.getString(mapRes, "VehicleID")+"','"+MapGetter.getString(mapRes, "Altitude")+"'");
				sql4.append(",'"+MapGetter.getString(mapRes, "GPSMile")+"' ,'"+MapGetter.getString(mapRes, "WorkTime")+"'");
				sql2.append(",'"+MapGetter.getString(mapRes, "PstnTime")+"'");
				sql5.append(",'"+MapGetter.getString(mapRes, "EgnWorkTime")+"'");
				sql3.append(" ,'"+MapGetter.getString(mapRes, "Speed")+"','"+MapGetter.getString(mapRes, "MsgTime")+"');");
				System.out.println("插入sql wei :"+sql.toString()+sql1+sql4+sql2+sql5+sql3);
				int n=baseDAO.execInsert(sql.toString()+sql1+sql4+sql2+sql5+sql3, new Object[]{});
				if(n>0){
					System.out.println("success");
				}else{
					System.out.println("failed");
				}
				 sql = new StringBuilder();
				 sql1 = new StringBuilder();
				 sql2 = new StringBuilder();
				 sql3 = new StringBuilder();
				 sql4 = new StringBuilder();
				 sql5 = new StringBuilder();
			}
		}
	}
	
	/**
	 * SQL集合批量执行
	 * @param sqlList
	 */
	public void executeSql(List<String> sqlList){
		tranDAO.batch(sqlList);
	}

	public int execInsert(String sql, Object[] objects) {
		// TODO Auto-generated method stub
		//baseDAO.execInsert(sql, objects);
		return baseDAO.execInsert(sql, objects);
	}

	public List<Map<String, Object>> getVclID(Map<String,Object> param) {
		String sql = "";
		String linkDataBase = "";
		if(null !=param){
			linkDataBase = "openrowset('SQLOLEDB','"+param.get("databaseVclView")+"';'"+param.get("user")+"';'"+param.get("password")+"',"+param.get("VclViewDataBase")+".dbo.V_VehicleInfo)";
			sql = "select VehicleID  from "+linkDataBase+" where UsefulLife>GETDATE()";
		}else{
			sql = "select VehicleID  from V_VehicleInfo where UsefulLife>GETDATE()";
		}
		
		List<Map<String,Object>> list = baseDAO.execQuery(sql, new Object[]{});
		return list;
	}
	//手动执行前先删除不全的数据
	public void deletVclRoad(String thisWorkDay) {
		String sql = "delete vcl_roadinfo_"+thisWorkDay;
		baseDAO.execUpdate(sql, new Object[]{});
		
	}
	//默认查询前三天的数据量
	public List<Map<String,Object>> getVclRoadInfoCount(List<String> list) {
		String sql  = "";
		List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
		for(String day : list){
			sql += "select count(1) as cont,'"+day+"' as workday from Vcl_RoadInfo_"+day.replaceAll("-", "");
			sql +=" union all ";
		}
		sql =sql.substring(0, sql.length()-10);
		result = baseDAO.execQuery(sql, new Object[]{});
		return result;
	}

	public int getExeVclRoadInfoCount(String isExectue) {
		String sql  = "select count(1) as cont from Vcl_RoadInfo_"+isExectue.replaceAll("-", "");
		Map<String,Object> map = baseDAO.execQueryOne(sql, new Object[]{});
		String result = map.get("cont").toString();
		return Integer.parseInt(result);
	}
	


}
